# Databricks notebook source
# MAGIC %md
# MAGIC ## TMR Populate Case Facts
# MAGIC <p><b>Description: </b>Add notebook description</p>
# MAGIC <b>Parent Process: </b>PL_FactPrism
# MAGIC <table class="table table-striped table-hover">
# MAGIC  <thead>
# MAGIC   <tr>
# MAGIC    <th>Contributor</th>
# MAGIC    <th>Date</th>
# MAGIC    <th>Version</th>
# MAGIC    <th>Comment</th>
# MAGIC    <th>WorkItem No</th>
# MAGIC   </tr>
# MAGIC  </thead>
# MAGIC  <tbody>
# MAGIC   <tr>
# MAGIC    <td>Iwan Dyke</td>
# MAGIC    <td>18/04/2024</td>
# MAGIC    <td>1.0</td>
# MAGIC    <td>Create initial release</td>
# MAGIC    <td></td>
# MAGIC   </tr>
# MAGIC  </tbody>
# MAGIC </table>

# COMMAND ----------

# MAGIC %md
# MAGIC ##Derive Case Dates Into Temporary View

# COMMAND ----------

# MAGIC %sql
# MAGIC CREATE OR REPLACE TEMPORARY VIEW Dates AS
# MAGIC SELECT DISTINCT
# MAGIC     f1.BPMReference,
# MAGIC     (
# MAGIC         SELECT MIN(f0.CreateDate) AS ApplicationDate
# MAGIC         FROM databricks_poc.tmr.task f0
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts1
# MAGIC             ON f0.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Review New Application' --Commented out for test data
# MAGIC         WHERE f0.BPMReference = f1.BPMReference
# MAGIC     ) AS DerivedApplicationDate,
# MAGIC     (
# MAGIC         SELECT MIN(f2.CompleteDate)
# MAGIC         FROM databricks_poc.tmr.dim_bpm_prefix dim_bpmp
# MAGIC         JOIN databricks_poc.tmr.task f2
# MAGIC             ON dim_bpmp.Code = f2.Dim_BPMPrefixCode
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts1
# MAGIC             ON f2.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Calculate Application Fee' --Commented out for test data
# MAGIC         WHERE f2.BPMReference = f1.BPMReference
# MAGIC     ) AS TechnicalApprovalDate,
# MAGIC     (
# MAGIC         SELECT MIN(f3.CreateDate)
# MAGIC         FROM databricks_poc.tmr.task f3
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts
# MAGIC             ON f3.Dim_TaskSubjectCode = dim_ts.Code
# MAGIC             --AND dim_ts.Description = 'Send Payment To PPU' --Commented out for test data
# MAGIC         WHERE f3.BPMReference = f1.BPMReference
# MAGIC     ) AS PPUGeneratedDate,
# MAGIC     (
# MAGIC         SELECT COALESCE(MIN(f4.CompleteDate), MIN(f5.CreateDate))
# MAGIC         FROM databricks_poc.tmr.task f4
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts1
# MAGIC             ON f4.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Finalise Letter for VAT Receipt' --Commented out for test data
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.task f5
# MAGIC             ON f4.BPMReference = f5.BPMReference
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.dim_task_subject dim_ts2
# MAGIC             ON f5.Dim_TaskSubjectCode = dim_ts2.Code
# MAGIC             --AND dim_ts2.Description = 'Print and Post' --Commented out for test data
# MAGIC         WHERE f4.BPMReference = f1.BPMReference
# MAGIC     ) AS VATReceiptSentDate,
# MAGIC     (
# MAGIC         SELECT MIN(f6.CreateDate)
# MAGIC         FROM databricks_poc.tmr.dim_bpm_prefix dim_bpmp
# MAGIC         JOIN databricks_poc.tmr.task f6
# MAGIC             ON dim_bpmp.Code = f6.Dim_BPMPrefixCode
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts1
# MAGIC             ON f6.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Initiate Legal Process' --Commented out for test data
# MAGIC         WHERE f6.BPMReference = f1.BPMReference
# MAGIC     ) AS LegalInitiatedDate,
# MAGIC     (
# MAGIC         SELECT COALESCE(MIN(f7.CompleteDate), MIN(f2.CompleteDate))
# MAGIC         FROM databricks_poc.tmr.task f7
# MAGIC         JOIN databricks_poc.tmr.dim_task_status dim_ts1
# MAGIC             ON f7.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Finalise Letter for Acknowledgement' --Commented out for test data
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.task f2
# MAGIC             ON f7.BPMReference = f2.BPMReference
# MAGIC             AND f7.CompleteDate = f2.StartDate
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.dim_task_subject dim_ts2
# MAGIC             ON f2.Dim_TaskSubjectCode = dim_ts2.Code
# MAGIC             --AND dim_ts2.Description = 'Print and Post' --Commented out for test data
# MAGIC         WHERE f7.BPMReference = f1.BPMReference
# MAGIC     ) AS LetterAcknowledgedDate,
# MAGIC     (
# MAGIC         SELECT COALESCE(MIN(f8.CompleteDate), MIN(f2.CompleteDate))
# MAGIC         FROM databricks_poc.tmr.task f8
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts1
# MAGIC             ON f8.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Finalise Letter for Request Missing Application Info' --Commented out for test data
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.task f2
# MAGIC             ON f8.BPMReference = f2.BPMReference
# MAGIC             AND f8.CompleteDate = f2.StartDate
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.dim_task_subject dim_ts2
# MAGIC             ON f2.Dim_TaskSubjectCode = dim_ts2.Code
# MAGIC             --AND dim_ts2.Description = 'Print and Post' --Commented out for test data
# MAGIC         WHERE f8.BPMReference = f1.BPMReference
# MAGIC     ) AS MissingInfoLetterDate,
# MAGIC     (
# MAGIC         SELECT MIN(f12.CreateDate)
# MAGIC         FROM databricks_poc.tmr.task f12
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts1
# MAGIC             ON f12.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             /*AND dim_ts1.Description = CASE                    --Commented out for test data
# MAGIC                 WHEN BPMReference LIKE 'PSA%' THEN 'Create Scheme'
# MAGIC                 WHEN BPMReference LIKE 'PSD%' THEN 'Assign Project Engineer'
# MAGIC                 WHEN BPMReference LIKE 'NCW%' THEN 'Select Correspondence Contact for VAT Receipt'
# MAGIC                 WHEN BPMReference LIKE 'WMD%' THEN 'Create Scheme'
# MAGIC                 WHEN BPMReference LIKE 'WMR%' THEN 'Create Scheme'
# MAGIC             END*/
# MAGIC         WHERE f12.BPMReference = f1.BPMReference
# MAGIC     ) AS TaskAfterMissingInfoReceivedDate,
# MAGIC     (
# MAGIC         SELECT COALESCE(MIN(f10.CompleteDate), MIN(f2.CompleteDate))
# MAGIC         FROM databricks_poc.tmr.task f10
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts1
# MAGIC             ON f10.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Finalise Letter for Request Revised Submission' --Commented out for test data
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.task f2
# MAGIC             ON f10.BPMReference = f2.BPMReference
# MAGIC             AND f10.CompleteDate = f2.StartDate
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.dim_task_subject dim_ts2
# MAGIC             ON f2.Dim_TaskSubjectCode = dim_ts2.Code
# MAGIC             --AND dim_ts2.Description = 'Print and Post' --Commented out for test data
# MAGIC         WHERE f10.BPMReference = f1.BPMReference
# MAGIC     ) AS RevisedSubmissionRequestedDate,
# MAGIC     (
# MAGIC         SELECT MIN(f11.CompleteDate)
# MAGIC         FROM databricks_poc.tmr.task f11
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts1
# MAGIC             ON f11.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Draft Agreement' --Commented out for test data
# MAGIC         WHERE f11.BPMReference = f1.BPMReference
# MAGIC     ) AS DraftAgreementDate,
# MAGIC     (
# MAGIC         SELECT COALESCE(MAX(f2.CompleteDate), MAX(f12.CreateDate))
# MAGIC         FROM databricks_poc.tmr.task f12
# MAGIC         JOIN databricks_poc.tmr.dim_task_status dim_ts1
# MAGIC             ON f12.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Finalise Letter for Sales Quotation' --Commented out for test data
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.task f2
# MAGIC             ON f12.BPMReference = f2.BPMReference
# MAGIC             AND f12.CompleteDate = f2.StartDate
# MAGIC         LEFT OUTER JOIN databricks_poc.tmr.dim_task_subject dim_ts2
# MAGIC             ON f2.Dim_TaskSubjectCode = dim_ts2.Code
# MAGIC             --AND dim_ts2.Description = 'Print and Post' --Commented out for test data
# MAGIC         WHERE f12.BPMReference = f1.BPMReference
# MAGIC     ) AS FirstQuotationSentDate,
# MAGIC     (
# MAGIC         SELECT MIN(f14.CompleteDate)
# MAGIC         FROM databricks_poc.tmr.task f14
# MAGIC         JOIN databricks_poc.tmr.dim_task_subject dim_ts1
# MAGIC             ON f14.Dim_TaskSubjectCode = dim_ts1.Code
# MAGIC             --AND dim_ts1.Description = 'Finalise Letter for Offer' --Commented out for test data
# MAGIC         WHERE f14.BPMReference = f1.BPMReference
# MAGIC     ) AS OfferSentDate
# MAGIC FROM databricks_poc.tmr.dim_bpm_prefix dim_bpmp
# MAGIC JOIN 
# MAGIC databricks_poc.tmr.task f1
# MAGIC     ON dim_bpmp.Code = f1.Dim_BPMPrefixCode;
# MAGIC
# MAGIC -- Clear DLT table
# MAGIC TRUNCATE TABLE databricks_poc.tmr.case;
# MAGIC
# MAGIC -- Populate TMR.Case FACT
# MAGIC INSERT INTO databricks_poc.tmr.case (
# MAGIC     BPMReference,
# MAGIC     Dim_BPMPrefixCode,
# MAGIC     StartDate,
# MAGIC     Dim_CaseStatusCode,
# MAGIC     Dim_ChannelCode,
# MAGIC     ApplicationDate,
# MAGIC     DerivedApplicationDate,
# MAGIC     DerivedApplicationDatePlus5,
# MAGIC     DerivedApplicationDatePlus14,
# MAGIC     DerivedApplicationDatePlus28,
# MAGIC     LetterAcknowledgedDate,
# MAGIC     AcknowledgedDate,
# MAGIC     MissingInfoLetterDate,
# MAGIC     MissingInfoReceivedDate,
# MAGIC     TaskAfterMissingInfoReceivedDate,
# MAGIC     RevisedSubmissionRequestedDate,
# MAGIC     TechnicalApprovalDate,
# MAGIC     LegalAndPaymentDate,
# MAGIC     DraftAgreementDate,
# MAGIC     PPUGeneratedDate,
# MAGIC     VATReceiptSentDate,
# MAGIC     LegalInitiatedDate,
# MAGIC     FirstQuotationSentDate,
# MAGIC     OfferSentDate,
# MAGIC     Dim_CaseJunkCode,
# MAGIC     OriginalEngineer,
# MAGIC     CurrentEngineer
# MAGIC )
# MAGIC SELECT DISTINCT
# MAGIC     ff.`BPM Reference Number` AS BPMReference,
# MAGIC     dim_bpmp.Code AS Dim_BPMPrefixCode,
# MAGIC     CAST(DATE(CASE
# MAGIC             WHEN LENGTH(ff.`Date Started`) = 10 THEN ff.`Date Started` --10 for test data, will need to be turned to 8 for production
# MAGIC             ELSE NULL
# MAGIC         END) AS DATE) AS StartDate,
# MAGIC     COALESCE(dim_cs.Code, -1) AS Dim_CaseStatusCode,
# MAGIC     COALESCE(dim_ch.Code, -1) AS Dim_ChannelCode,
# MAGIC     CAST(DATE(CASE
# MAGIC             WHEN LENGTH(ff.`Application Date`) = 10 THEN ff.`Application Date`--10 for test data, will need to be turned to 8 for production
# MAGIC             ELSE NULL
# MAGIC         END) AS DATE) AS ApplicationDate,
# MAGIC     d.DerivedApplicationDate,
# MAGIC     DATEADD(day, 5, d.DerivedApplicationDate),
# MAGIC     DATEADD(day, 14, d.DerivedApplicationDate),
# MAGIC     DATEADD(day, 28, d.DerivedApplicationDate),
# MAGIC     d.LetterAcknowledgedDate,
# MAGIC     COALESCE(d.MissingInfoLetterDate, d.LetterAcknowledgedDate, d.FirstQuotationSentDate) AS AcknowledgedDate,
# MAGIC     d.MissingInfoLetterDate,
# MAGIC     CASE
# MAGIC         WHEN d.MissingInfoLetterDate IS NOT NULL THEN d.TaskAfterMissingInfoReceivedDate
# MAGIC         ELSE NULL
# MAGIC     END AS MissingInfoReceivedDate,
# MAGIC     d.TaskAfterMissingInfoReceivedDate,
# MAGIC     d.RevisedSubmissionRequestedDate,
# MAGIC     d.TechnicalApprovalDate,
# MAGIC       CASE
# MAGIC         WHEN LegalInitiatedDate > VATReceiptSentDate THEN LegalInitiatedDate
# MAGIC         WHEN LegalInitiatedDate <= VATReceiptSentDate THEN COALESCE(PPUGeneratedDate, VATReceiptSentDate)
# MAGIC     END AS LegalAndPaymentDate,
# MAGIC     d.DraftAgreementDate,
# MAGIC     d.PPUGeneratedDate,
# MAGIC     d.VATReceiptSentDate,
# MAGIC     d.LegalInitiatedDate,
# MAGIC     d.FirstQuotationSentDate,
# MAGIC     d.OfferSentDate,
# MAGIC     COALESCE(dim_cj.Code, -1) AS Dim_CaseJunkCode,
# MAGIC     COALESCE(dim_ce1.Code, -2) AS OriginalEngineer,
# MAGIC     COALESCE(dim_ce2.Code, -2) AS CurrentEngineer
# MAGIC FROM 
# MAGIC     databricks_poc.staging.tmr_raw ff
# MAGIC LEFT JOIN databricks_poc.tmr.dim_case_status dim_cs
# MAGIC     ON ff.`Case Status` = dim_cs.Description
# MAGIC LEFT JOIN databricks_poc.tmr.dim_channel dim_ch
# MAGIC     ON ff.Channel = dim_ch.Description
# MAGIC LEFT JOIN databricks_poc.tmr.dim_bpm_prefix dim_bpmp
# MAGIC     ON LEFT(ff.`BPM Reference Number`, 3) = dim_bpmp.Prefix
# MAGIC LEFT JOIN databricks_poc.tmr.dim_casejunk dim_cj
# MAGIC     ON ff.`BPM Reference Number` = dim_cj.BPMReference
# MAGIC     AND ff.`Applicant Company` = dim_cj.Company
# MAGIC     AND ff.`Applicant Name` = dim_cj.Applicant
# MAGIC LEFT JOIN databricks_poc.tmr.dim_case_engineer dim_ce1
# MAGIC     ON ff.`BPM Reference Number` = dim_ce1.BPMReference
# MAGIC     AND dim_ce1.OriginalEngineer = 1
# MAGIC LEFT JOIN databricks_poc.tmr.dim_case_engineer dim_ce2
# MAGIC     ON ff.`BPM Reference Number` = dim_ce2.BPMReference
# MAGIC     AND dim_ce2.CurrentFlag = 1
# MAGIC LEFT JOIN Dates d
# MAGIC     ON ff.`BPM Reference Number` = d.BPMReference
# MAGIC WHERE 
# MAGIC     ff.`BPM Reference Number` <> '';
# MAGIC
# MAGIC -- FullApplicationReceivedDate
# MAGIC UPDATE databricks_poc.tmr.case
# MAGIC SET FullApplicationReceivedDate = 
# MAGIC     CASE 
# MAGIC         WHEN MissingInfoReceivedDate IS NOT NULL THEN TaskAfterMissingInfoReceivedDate
# MAGIC         WHEN DerivedApplicationDate > '20141101' THEN DerivedApplicationDate
# MAGIC     END;
# MAGIC
# MAGIC /*
# MAGIC -- FullApplicationReceivedDate for WMR
# MAGIC WITH CTE AS
# MAGIC (
# MAGIC     SELECT
# MAGIC         f12.BPMReference,
# MAGIC         MAX(CreateDate) AS CreateDate
# MAGIC     FROM 
# MAGIC         databricks_poc.tmr.task f12
# MAGIC         JOIN databricks_poc.tmr.dim_process dim_ts1
# MAGIC         ON f12.Dim_ProcessCode = dim_ts1.Code
# MAGIC         AND dim_ts1.Description = 'Application Confirmation'
# MAGIC     GROUP BY f12.BPMReference
# MAGIC )
# MAGIC UPDATE databricks_poc.tmr.case
# MAGIC SET FullApplicationReceivedDate = CTE.CreateDate
# MAGIC FROM
# MAGIC     databricks_poc.tmr.case c
# MAGIC     JOIN CTE 
# MAGIC     ON c.BPMReference = CTE.BPMReference
# MAGIC     JOIN databricks_poc.tmr.dim_bpm_prefix p
# MAGIC     ON c.Dim_BPMPrefixCode = p.Code
# MAGIC WHERE p.Prefix in ('WMR','NCW');
# MAGIC */
# MAGIC
